package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.bean.InstrumentBean;
import com.db.JdbcUtil;
import com.exception.DBException;
import com.exception.InstrumentException;

public class InstrumentDao
{
	private Connection con = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	ArrayList<InstrumentBean> list = new ArrayList<InstrumentBean>();

	public void addInstruments(InstrumentBean inst) throws DBException,
			InstrumentException
	{
		String sql = "insert into lab_instrument(barcode,name,ifin,price,remark,date)"
				+ " values(?,?,?,?,?,curdate())";

		con = JdbcUtil.getConnection();
		try
		{

			ps = con.prepareStatement(sql);
			ps.setString(1, inst.getBarcode());
			ps.setString(2, inst.getName());
			ps.setString(3, "yes");
			ps.setString(4, inst.getPrice());
			ps.setString(5, inst.getRemark());
			ps.executeUpdate();
		}
		catch (Exception e)
		{
			throw new InstrumentException("fail to add instrument !");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}

	}

	public void updateInstruments(InstrumentBean inst) throws DBException,
			InstrumentException
	{

		String sql = "update lab_instrument set name=?,price=?,remark=? where barcode=?";
		con = JdbcUtil.getConnection();
		try
		{
			ps = con.prepareStatement(sql);
			ps.setString(1, inst.getName());
			ps.setString(2, inst.getPrice());
			ps.setString(3, inst.getRemark());
			ps.setString(4, inst.getBarcode());
			ps.executeUpdate();
		}
		catch (Exception e)
		{
			throw new InstrumentException("fail to update instrument !");
		}

		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
	}

	public void deleteInstruments(InstrumentBean inst) throws DBException,
			InstrumentException
	{
		String sql = "delete from lab_instrument where barcode=?";
		String sql_ifin = "select ifin from lab_instrument where barcode="
				+ "'" + inst.getBarcode() + "'";
		con = JdbcUtil.getConnection();
		try
		{
			Statement stmt = con.createStatement();
			rs = stmt.executeQuery(sql_ifin);
			while (rs.next())
			{
				if (rs.getString(1).equals("no"))
					throw new InstrumentException(
							"the Instrument that is keeping by user <br> you can't delete it, please check!");
			}

			try
			{
				ps = con.prepareStatement(sql);
				ps.setString(1, inst.getBarcode());
				ps.executeUpdate();
			}
			catch (SQLException e)
			{
				throw new InstrumentException("fail to delete instrument !");
			}
		}
		catch (SQLException e1)
		{
			throw new InstrumentException(
					"fail to check instrument , please try again!");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}

	}

	public ArrayList<InstrumentBean> getInstrumentsByBarcode(InstrumentBean inst)
			throws DBException, InstrumentException
	{

		String sql = "select barcode,name,ifin,price,remark,date from lab_instrument"
				+ " where barcode like '%" + inst.getBarcode() + "%'";

		con = JdbcUtil.getConnection();
		try
		{
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next())
			{
				inst = new InstrumentBean();
				inst.setBarcode(rs.getString("barcode"));
				inst.setName(rs.getString("name"));
				inst.setIfin(rs.getString("ifin"));
				inst.setRemark(rs.getString("remark"));
				inst.setPrice(rs.getString("price"));
				inst.setDate(rs.getDate("date"));
				list.add(inst);
			}
		}
		catch (Exception e)
		{
			throw new InstrumentException(
					"fail to query instrument by barcode!");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return list;
	}

	public boolean ifInsExist(InstrumentBean inst) throws DBException,
			InstrumentException
	{

		String sql = "select barcode from lab_instrument"
				+ " where barcode = '" + inst.getBarcode() + "'";
		boolean flag = false;
		con = JdbcUtil.getConnection();
		try
		{
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next())
			{
				flag = true;
			}
		}
		catch (Exception e)
		{
			throw new InstrumentException(
					"Fail to justify if the instrument is exist");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return flag;
	}

	public String ifInsBorrowed(InstrumentBean inst) throws DBException,
			InstrumentException
	{

		String sql = "select ifin from lab_instrument" + " where barcode = '"
				+ inst.getBarcode() + "'";
		String ifin = "";
		con = JdbcUtil.getConnection();
		try
		{
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next())
			{
				ifin = rs.getString(1);
			}
		}
		catch (Exception e)
		{
			throw new InstrumentException(
					"Fail to justify if the instrument be borrowed");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return ifin;
	}

	public ArrayList<InstrumentBean> getInstrumentsByName(InstrumentBean inst)
			throws DBException, InstrumentException
	{

		String sql = "select barcode,name,ifin,price,remark,date from lab_instrument"
				+ " where name like '%" + inst.getName() + "%'";
		con = JdbcUtil.getConnection();
		try
		{
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next())
			{
				inst = new InstrumentBean();
				inst.setBarcode(rs.getString("barcode"));
				inst.setName(rs.getString("name"));
				inst.setIfin(rs.getString("ifin"));
				inst.setRemark(rs.getString("remark"));
				inst.setPrice(rs.getString("price"));
				inst.setDate(rs.getDate("date"));
				list.add(inst);
			}
		}
		catch (Exception e)
		{
			throw new InstrumentException("fail to query instrument by name!");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return list;
	}

	public void getInstrumentsDetail(InstrumentBean inst) throws DBException,
			InstrumentException
	{
		String sql = "select * from lab_instrument" + " where barcode = '"
				+ inst.getBarcode() + "'";

		con = JdbcUtil.getConnection();
		try
		{
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next())
			{
				inst.setBarcode(rs.getString("barcode"));
				inst.setName(rs.getString("name"));
				inst.setIfin(rs.getString("ifin"));
				inst.setRemark(rs.getString("remark"));
				inst.setPrice(rs.getString("price"));
				inst.setDate(rs.getDate("date"));
			}
		}
		catch (Exception e)
		{
			throw new InstrumentException(
					"fail to query instrument by barcode!");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
	}
}
